package org.qiancizhan.Mapper;

import org.qiancizhan.Config.MySQL;
import org.qiancizhan.Model.DTO.BookWordsDTO;
import org.qiancizhan.Model.POJO.WordPOJO;
import org.qiancizhan.Model.VO.WordsVO;

import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

public class WordMapper {
    private final MySQL mysql = MySQL.getInstance();


    public WordsVO getWords(BookWordsDTO bookWordsDTO) {
        int pageSize = bookWordsDTO.getPageConfig().getPageSize();
        int currentNum = bookWordsDTO.getPageConfig().getCurrentNum();
        int offset = (currentNum - 1) * pageSize;
        String sql = String.format(
                "SELECT * FROM %s LIMIT %d OFFSET %d",
                bookWordsDTO.getBookName(),
                pageSize,
                offset
        );
        List<WordPOJO> words = mysql.executeQuery(String.format(sql, bookWordsDTO.getBookName()), rs -> {
            WordPOJO wordPOJO = new WordPOJO();
            wordPOJO.setWord(rs.getString("word"));
            wordPOJO.setTranslate(rs.getString("translate"));
            return wordPOJO;
        });
        bookWordsDTO.getPageConfig().setTotalNum((int) Math.ceil(bookWordsDTO.getWordCount() * 1.0 / pageSize));
        return new WordsVO(words, bookWordsDTO.getPageConfig());
    }

    public List<WordPOJO> getAllWords() {
        String sql = """
                SELECT source, word, translate FROM (
                    SELECT
                        source,
                        word,
                        translate,
                        ROW_NUMBER() OVER (PARTITION BY word ORDER BY source) as rn
                    FROM (
                        SELECT 'cet4' as source, word, translate FROM cet4
                        UNION ALL
                        SELECT 'cet6' as source, word, translate FROM cet6
                        UNION ALL
                        SELECT 'graduate' as source, word, translate FROM graduate
                        UNION ALL
                        SELECT 'junior' as source, word, translate FROM junior
                        UNION ALL
                        SELECT 'sat' as source, word, translate FROM sat
                        UNION ALL
                        SELECT 'senior' as source, word, translate FROM senior
                        UNION ALL
                        SELECT 'toefl' as source, word, translate FROM toefl
                    ) AS all_words
                ) AS ranked_words
                WHERE rn = 1
                """;
        return mysql.executeQuery(sql, rs -> {
            WordPOJO wordPOJO = new WordPOJO();
            wordPOJO.setWord(rs.getString("word"));
            wordPOJO.setTranslate(rs.getString("translate"));
            return wordPOJO;
        });
    }

    public List<WordPOJO> getNumWords(int num) {
        List<WordPOJO> allWords = getAllWords();
        Collections.shuffle(allWords);
        return allWords.stream().limit(num).toList();
    }

    public List<WordPOJO> getWordInfo(String input, boolean isEnglish) {
        List<WordPOJO> allWords = getAllWords();
        if (input == null || input.trim().isEmpty())
            return null;
        String searchTerm = input.trim();
        return allWords.stream()
                .filter(word -> {
                    if (isEnglish) {
                        return word.getWord() != null &&
                                word.getWord().toLowerCase().contains(searchTerm);
                    } else {
                        return word.getTranslate() != null &&
                                word.getTranslate().contains(searchTerm);
                    }
                })
                .collect(Collectors.toList());
    }
}
